﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using OgilvyOne;
using System.Data;



namespace OgilvyOne.BLL
{
    public class BLLUserInfo
    {
        private static string _sql;
        private static IDAL.IDALUserInfo iuser=DALFactory.CreateInstance.CreateDALUserInfo();
       
        public static int update(string status, string userId)
        {
            string sql = string.Format("update golf_UserInfo set status='{0}' where userId={1}", status, userId);
            return iuser.insert(sql, new SqlParameter[0]);
        }
        public static int insert(Model.Golf_UserInfo user)
        {
            _sql = Utility.SqlTool.buildInsertSql(user);
            return iuser.getScalar(_sql);
        }
        public static int checkEmail(string email)
        {
            string sql = string.Format("select count(email) from golf_UserInfo where email='{0}' and status=1", email);
            return iuser.select(sql);
        }

        public static int checkName(string username)
        {
            string sql = string.Format("select count(userName) from golf_UserInfo where userName='{0}' and status=1", username);
            return iuser.select(sql);
        }
        public static DataTable getAll()
        {
            _sql = "select * from golf_UserInfo order by userId desc";
            return iuser.query(_sql);
        }

        public static DataTable getAllEmail()
        {
            _sql = "select * from golf_Email order by emailId desc";
            return iuser.query(_sql);
        }

        public static DataTable getUserList(Model.QueryCondtion qc)
        {
            StringBuilder sqlBuilder = new StringBuilder("select * from golf_UserInfo where 1=1");
            if (qc != null)
            {
                if (!string.IsNullOrEmpty(qc.StartTime) && !string.IsNullOrEmpty(qc.EndTime))
                {
                    sqlBuilder.Append(string.Format(" and  registerDate between '{0}' and '{1}' ", qc.StartTime, qc.EndTime));
                }
                else if (!string.IsNullOrEmpty(qc.StartTime))
                {
                    sqlBuilder.Append(string.Format(" and  registerDate between '{0}' and '{1}' ", qc.StartTime, DateTime.Now.ToShortDateString()));
                }
                else if (!string.IsNullOrEmpty(qc.EndTime))
                {
                    sqlBuilder.Append(string.Format(" and  registerDate <= '{0}' ", qc.EndTime));
                }
            }
            sqlBuilder.Append(" order by registerDate desc");
            return iuser.query(sqlBuilder.ToString());
        }

        public static DataTable getEmailList(Model.QueryCondtion qc)
        {
            StringBuilder sqlBuilder = new StringBuilder("select * from golf_Email where 1=1");
            if (qc != null)
            {
                if (!string.IsNullOrEmpty(qc.StartTime) && !string.IsNullOrEmpty(qc.EndTime))
                {
                    sqlBuilder.Append(string.Format(" and  creatTime between '{0}' and '{1}' ", qc.StartTime, qc.EndTime));
                }
                else if (!string.IsNullOrEmpty(qc.StartTime))
                {
                    sqlBuilder.Append(string.Format(" and  creatTime between '{0}' and '{1}' ", qc.StartTime, DateTime.Now.ToShortDateString()));
                }
                else if (!string.IsNullOrEmpty(qc.EndTime))
                {
                    sqlBuilder.Append(string.Format(" and  creatTime <= '{0}' ", qc.EndTime));
                }
            }
            sqlBuilder.Append(" order by creatTime desc");
            return iuser.query(sqlBuilder.ToString());
        }

        public static int delData(string tableName)
        {
            string sql = string.Format("delete from {0}", tableName);
            return iuser.delete(sql);
        }

        public static DataTable getGraffiti()
        {
            _sql = "select * from golf_Graffiti";
            return iuser.query(_sql);
        }
        public static int insData(string pic, string title, string author)
        {                                  //返回新插入行的标识列(id)的值 ;SELECT @@IDENTITY
            string sql = string.Format("insert into golf_Graffiti (pic,title,author) VALUES('{0}','{1}','{2}');SELECT @@IDENTITY", pic, title, author);
            return iuser.getScalar(sql);
        }
        public static int insData(string pic, string title, string author, string source)
        {                                  //返回新插入行的标识列(id)的值 ;SELECT @@IDENTITY
            string sql = string.Format("insert into golf_Graffiti (pic,title,author,source) VALUES('{0}','{1}','{2}','{3}');SELECT @@IDENTITY", pic, title, author, source);
            return iuser.getScalar(sql);
        }

        public static int insInto(string pic,string name)
        {                                  //返回新插入行的标识列(id)的值 ;SELECT @@IDENTITY
            string sql = string.Format("insert into golf_Photographer (pic,name) VALUES('{0}','{1}');SELECT @@IDENTITY", pic, name);
            return iuser.getScalar(sql);
        }
        public static int insInto(string pic, string name,string source)
        {                                  //返回新插入行的标识列(id)的值 ;SELECT @@IDENTITY
            string sql = string.Format("insert into golf_Photographer (pic,name,source) VALUES('{0}','{1}','{2}');SELECT @@IDENTITY", pic, name, source);
            return iuser.getScalar(sql);
        }
        public static int count()
        {
            string sql = string.Format("select count(id) from golf_Graffiti where status=1");
            return iuser.select(sql);
        }

         
         public static int insInvite(Model.Golf_Invite user)
         {
             string sql = Utility.SqlTool.buildInsertSql(user);
             return iuser.getScalar(sql);
         }
         public static int ckEmailInvite(string herEmail)
         {
             string sql = string.Format("select count(herEmail) from golf_Invite where herEmail='{0}' and status=1", herEmail);
             return iuser.select(sql);
         }
         public static DataTable GetAllGraffiti(string max,string pageNum )
         {
             string sql = string.Format("select top {0} * from golf_Graffiti where (  id not in(select top {1} id from golf_Graffiti order by id desc))and status=1 order by id desc", max, pageNum);
             return iuser.query(sql);
         }
         public static DataTable Getdate(string id)
         {
             string sql = string.Format("select creatTime from golf_Graffiti where id={0} ", id);
             return iuser.query(sql);
         }
         public static DataTable GetWork(string id)
         {
             string sql = string.Format("select * from golf_Graffiti where id={0}", id);
             return iuser.query(sql);
         }
         public static DataTable GetPhoto(string id)
         {
             string sql = string.Format("select * from golf_Photographer where id={0} and status=1", id);
             return iuser.query(sql);
         }


         //public static DataTable getPic(string picName)
         //{
         //    string sql = string.Format("select * from golf_Graffiti where picName={0}", picName);
         //    return iuser.query(sql);
         //}
         //public static int checkWorks(string works)
         //{
         //    string sql = string.Format("select count(works) from golf_Graffiti where works='{0}' and status=1", works);
         //    return iuser.select(sql);
         //}

        /**
        public static DataTable getProvince(string tableName)
        {
            _sql = string.Format("select * from {0}", tableName);
            return iuser.query(_sql);
        }

        public static DataTable getCity(string tableName, string provinceId)
        {
            _sql = string.Format("select * from {0} where province_id={1}", tableName, provinceId);
            return iuser.query(_sql);
        }

        public static DataTable getDealer(string tableName, string provinceName,string cityName)
        {
            _sql = string.Format("select * from {0} where province={1} and city={2}", tableName, provinceName, cityName);
            return iuser.query(_sql);
        }
        */
        public static int update()
        {
            string sql = string.Format("update SYS_MENU set menu_name='高尔夫',MENU_DISPLAY_NAME='高尔夫' where menu_uid=1064");
            return iuser.insert(sql, new SqlParameter[0]);
        }

        public static DataTable getalldata()
        {
            _sql = "select * from golf_Graffiti order by id desc";
            return iuser.query(_sql);
        }
        //审核涂鸦
        public static int reviewGraffiti(string status, string id)
        {
            string sql = string.Format("update golf_Graffiti set status={0} where id={1}", status, id);
            return iuser.insert(sql);
        }

        //删除单个涂鸦
        public static int deleteGraffiti(string id)
        {
            string sql = string.Format("delete from golf_Graffiti where id={0}", id);
            return iuser.delete(sql);
        }
        //保存email到数据库
        public static int insEmail(string Emai,string source)
        {                                  //返回新插入行的标识列(id)的值 ;SELECT @@IDENTITY
            string sql = string.Format("insert into golf_Email (email,source) VALUES('{0}','{1}');SELECT @@IDENTITY", Emai, source);
            return iuser.getScalar(sql);
        }

    }
}
